{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# DataWhale Mysql Task_02"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-------prodtct表内容如下\r\n",
    "---具体见shop.sql文件\r\n",
    "INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');\r\n",
    "INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');\r\n",
    "INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);\r\n",
    "INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');\r\n",
    "INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');\r\n",
    "INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');\r\n",
    "INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');\r\n",
    "INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');\r\n",
    "COMMIT;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * 1.查询与排序"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "USE shop;\r\n",
    "SELECT * FROM product;\r\n",
    "SELECT product_name, product_type\r\n",
    "  FROM product\r\n",
    " WHERE product_type = '衣服';\r\n",
    " SELECT product_name\r\n",
    "  FROM product\r\n",
    " WHERE product_type = '衣服';"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_id     As id,\r\n",
    "       product_name   As name,\r\n",
    "       purchase_price AS 进货单价\r\n",
    "  FROM product;\r\n",
    "  SELECT DISTINCT product_type\r\n",
    "  FROM product;\r\n",
    "  SELECT * \r\n",
    "  FROM product\r\n",
    "  WHERE purchase_price > 100\r\n",
    "  GROUP BY purchase_price ;\r\n",
    "\r\n",
    "  SELECT product_name, product_type, sale_price\r\n",
    "  FROM product\r\n",
    " WHERE sale_price >= 1000;\r\n",
    "\r\n",
    " SELECT product_name, product_type, sale_price\r\n",
    "  FROM product\r\n",
    " WHERE NOT sale_price >= 1000;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--注意逻辑运算符的优先级\r\n",
    "SELECT product_name, product_type, regist_date\r\n",
    "  FROM product\r\n",
    " WHERE product_type = '办公用品'\r\n",
    "   AND regist_date = '2009-09-11'\r\n",
    "    OR regist_date = '2009-09-20';\r\n",
    "SELECT product_name, product_type, regist_date\r\n",
    "  FROM product\r\n",
    " WHERE product_type = '办公用品'\r\n",
    "   AND ( regist_date = '2009-09-11'\r\n",
    "        OR regist_date = '2009-09-20');"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT * FROM product"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 练习题\r\n",
    "## * Q2.1"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name , regist_date\r\n",
    "    FROM product\r\n",
    "    WHERE regist_date > '2009-04-28'"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * Q2.2"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--1.叉子和圆珠笔行\r\n",
    "SELECT *\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price IS NULL;\r\n",
    " --啥都不是\r\n",
    " SELECT *\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price = NULL;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--1.purchase_price非空行\r\n",
    "SELECT *\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price IS NOT NULL;\r\n",
    "  --啥都不是\r\n",
    "SELECT *\r\n",
    "  FROM product\r\n",
    " WHERE purchase_price <> NULL;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    " --啥都不是\r\n",
    "SELECT *\r\n",
    "  FROM product\r\n",
    " WHERE product_name > NULL;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * Q2.3"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name , sale_price , purchase_price \r\n",
    "  FROM product\r\n",
    " WHERE sale_price - purchase_price >= 500;\r\n",
    "--经典取反\r\n",
    " SELECT product_name , sale_price , purchase_price \r\n",
    "  FROM product\r\n",
    " WHERE NOT sale_price - purchase_price < 500;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * Q2.4"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_name, product_type,\r\n",
    "    (sale_price * 0.9 - purchase_price) AS profit\r\n",
    "    FROM product\r\n",
    "    WHERE (sale_price * 0.9 - purchase_price) > 100;\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "---报错，别名,执行顺序\r\n",
    "SELECT product_name, product_type,\r\n",
    "    (sale_price * 0.9 - purchase_price) AS profit\r\n",
    "    FROM product\r\n",
    "    WHERE profit > 100;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- ALTER TABLE product ADD COLUMN profit\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT *\r\n",
    "  FROM product;\r\n",
    "SELECT COUNT(*)----有NULL,仍然计算\r\n",
    "  FROM product;\r\n",
    "SELECT COUNT(purchase_price)\r\n",
    "  FROM product;\r\n",
    "\r\n",
    "SELECT SUM(sale_price), SUM(purchase_price) \r\n",
    "  FROM product;\r\n",
    "SELECT SUM(sale_price), SUM(DISTINCT sale_price)\r\n",
    " FROM product;\r\n",
    "SELECT AVG(sale_price), AVG(purchase_price)\r\n",
    "  FROM product;\r\n",
    "SELECT MAX(regist_date), MIN(regist_date)\r\n",
    "  FROM product;\r\n",
    "SELECT COUNT(DISTINCT product_type)\r\n",
    " FROM product;\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_type, COUNT(*)\r\n",
    "    FROM product\r\n",
    "    GROUP BY product_type;\r\n",
    "SELECT product_type, COUNT(*)\r\n",
    "    FROM product\r\n",
    "    GROUP BY product_type DESC;\r\n",
    "SELECT product_type, COUNT(*)\r\n",
    "    FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT purchase_price, COUNT(*)\r\n",
    "  FROM product\r\n",
    " GROUP BY purchase_price;\r\n",
    " SELECT * FROM product;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--!!!!!!!!--SELECT子句在GROUP BY子句后执行。\r\n",
    "SELECT product_name,purchase_price, COUNT(*)\r\n",
    "  FROM product\r\n",
    " WHERE product_type = '衣服'\r\n",
    " GROUP BY purchase_price;\r\n",
    "SELECT product_type, COUNT(*)\r\n",
    "  FROM product\r\n",
    "  GROUP BY product_type\r\n",
    "  HAVING product_type = '办公用品';\r\n",
    "-- 错误形式（因为product_name不包含在GROUP BY聚合键中）\r\n",
    "SELECT product_name,purchase_price, COUNT(*)\r\n",
    "  FROM product\r\n",
    " GROUP BY product_type\r\n",
    "HAVING product_name = '圆珠笔';"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 数字\r\n",
    "SELECT product_type, COUNT(*)\r\n",
    "    FROM product\r\n",
    "    GROUP BY product_type\r\n",
    "    HAVING COUNT(*) = 4;---最后执行，并且针对分组后的数据"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "---- 聚合函数的使用前提是结果集已经确定\r\n",
    "---因为 COUNT(*)的计算结果后于WHERE\r\n",
    "----以下报错\r\n",
    "SELECT product_name,purchase_price, COUNT(*)\r\n",
    "    FROM product\r\n",
    "    WHERE COUNT(*) > 2  \r\n",
    "    GROUP BY purchase_price;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 这是因为SQL在使用 HAVING 子句时 SELECT 语句的顺序为：\r\n",
    "\r\n",
    "## * FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * Q2.5"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT product_id, SUM（product_name）\r\n",
    "--本SELECT语句中存在错误。\r\n",
    "  FROM product \r\n",
    " GROUP BY product_type \r\n",
    " WHERE regist_date > '2009-09-01';\r\n",
    "\r\n",
    "---SUM有问题,总和应该是有组的product_type\r\n",
    "---WHERE位置有问题\r\n",
    " SELECT product_id,product_type, COUNT(product_type)\r\n",
    "    FROM product \r\n",
    "    WHERE regist_date > '2009-09-01'\r\n",
    "    GROUP BY product_type ;\r\n",
    "\r\n",
    " SELECT * FROM product;\r\n",
    "\r\n",
    " SELECT product_id, COUNT(product_name)\r\n",
    "    FROM product \r\n",
    "    WHERE regist_date > '2009-09-01'\r\n",
    "    GROUP BY product_type  \r\n",
    "    HAVING COUNT(product_name) > 1;\r\n",
    "  "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * Q2.6"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    " SELECT product_type , \r\n",
    "        SUM(sale_price) ,\r\n",
    "        SUM(purchase_price) \r\n",
    "    FROM product \r\n",
    "    -- WHERE regist_date > '2009-09-01'\r\n",
    "    GROUP BY product_type  \r\n",
    "    HAVING SUM(sale_price) >  1.5 * SUM(purchase_price);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    " SELECT product_type AS \"商品终类\", \r\n",
    "        SUM(sale_price) AS \"总售价\",\r\n",
    "        SUM(purchase_price) AS \"总进价\"\r\n",
    "    FROM product \r\n",
    "    GROUP BY product_type  \r\n",
    "    HAVING SUM(sale_price) >  1.5 * SUM(purchase_price) \r\n",
    "    ORDER BY \"总售价\"   DESC;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## * Q2.7"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    " SELECT * FROM product;\r\n",
    " SELECT *\r\n",
    "    FROM product \r\n",
    "    ORDER BY  regist_date IS NULL DESC,regist_date DESC,sale_price;\r\n",
    "-----如何把运动T恤放前面，此处很经典\r\n",
    "-----bingo"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Task_02      \r\n",
    "## END           2021/8/19"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}